package tgc.edu.fhh.service;



import java.util.List;

import javax.annotation.Resource;
import javax.persistence.EntityManager;
import javax.persistence.Query;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import tgc.edu.fhh.custom.BaseService;
import tgc.edu.fhh.entity.Course;
import tgc.edu.fhh.entity.Grade;
import tgc.edu.fhh.entity.Student;
import tgc.edu.fhh.repository.GradeRepository;
@Service
public class GradeService extends BaseService<Grade, Integer>{
	@Autowired
	private GradeRepository gradedao;
	@Resource
	private EntityManager entityManager;
	public List<Course> findCoursesByClassId(Integer classId) {
		return gradedao.findCourseByTbclassId(classId);
	}

	public List<Grade> findByTbClassIdAndCourseId(Integer bjId, Integer couId) {
		return gradedao.findByTbClassIdAndCourseId(bjId,couId);
	}

	public List<Grade> findByTbClassIdAndStudentId(Integer bjId, Integer stuId) {
		return gradedao.findByTbClassIdAndStudentId(bjId,stuId);
	}

	public List<Grade> findByStudentId(Integer id) {
		return gradedao.findByStudentId(id);
	}
	
	public List<Object[]> getTableDataByCoursesAndTbClassId(List<Course> courses,Integer classId) {
		String hql="select t.student.stuNo as 学号,t.student.name as 姓名 ";
		for (Course course : courses) {
			hql+=",sum(case when t.course.id="+course.getId()+" then t.grade else 0 end) as "+course.getCourseName()+" ";
		}
		hql+=",sum(t.grade) as 总分 ";
		hql+="from Grade t ";
		hql+="where t.student.tbclass.id="+classId;
		hql+=" group by t.student order by 总分 desc";
		Query query = entityManager.createQuery(hql);
		@SuppressWarnings("unchecked")
		List<Object[]> list = query.getResultList();
		return list;
	}

	public List<Student> findStudentByCourseId(Integer courseId) {
		
		return gradedao.findStudentByCourseId(courseId);
	}


}
